GSC Analysis Template

by @EntitiesChecker

Data: monthly queries by country (clicks, impressions, ctr, position)

country query clicks impressions ctr position date
0 usa advertools 40 60 0.666667 1.016667 2023-01-31
1 ind advertools 21 35 0.600000 1.000000 2023-01-31
2 ind log analysis using python 17 28 0.607143 1.000000 2023-01-31
3 gbr advertools 15 25 0.600000 1.000000 2023-01-31
4 deu advertools 10 20 0.500000 1.000000 2023-01-31
... ... ... ... ... ... ... ...
205890 zmb video sitemaps 0 1 0.000000 63.000000 2023-09-30
205891 zwe crawling seo 0 1 0.000000 95.000000 2023-09-30
205892 zwe download sitemap 0 1 0.000000 12.000000 2023-09-30
205893 zwe get sitemap xml 0 1 0.000000 47.000000 2023-09-30
205894 zwe seo library 0 1 0.000000 64.000000 2023-09-30

205895 rows × 7 columns

Monthly metrics by page and country

page country clicks impressions ctr position date
0 https://advertools.readthedocs.io/ usa 51 391 0.130435 47.304348 2023-01-31
1 https://advertools.readthedocs.io/en/master/advertools.logs.html ind 49 688 0.071221 21.985465 2023-01-31
2 https://advertools.readthedocs.io/ ind 28 108 0.259259 27.962963 2023-01-31
3 https://advertools.readthedocs.io/en/master/advertools.logs.html usa 21 2350 0.008936 30.180851 2023-01-31
4 https://advertools.readthedocs.io/ gbr 16 53 0.301887 32.056604 2023-01-31
... ... ... ... ... ... ... ...
24698 https://advertools.readthedocs.io/en/master/readme.html tun 0 1 0.000000 1.000000 2023-09-30
24699 https://advertools.readthedocs.io/en/master/readme.html tur 0 6 0.000000 16.666667 2023-09-30
24700 https://advertools.readthedocs.io/en/master/readme.html twn 0 4 0.000000 42.250000 2023-09-30
24701 https://advertools.readthedocs.io/en/master/readme.html ukr 0 2 0.000000 1.000000 2023-09-30
24702 https://advertools.readthedocs.io/en/master/readme.html vnm 0 9 0.000000 15.333333 2023-09-30

24703 rows × 7 columns

Create interesting subsets of the data

Most frequent words

The wtd_freq column shows the total impressions of all queries containing the respective word. For example, “seo” has appeared in many different queries, the total impressions of which was 123,266.

We can now take the top words, create their respective regular expressions, and create special columns for each term.

term regex
0 brand advertool
1 seo seo
2 python python
3 sitemap sitemap|xml
4 analysis analy[sz]|analyt
5 robots robots
6 crawl crawl|scrap[ei]|spider
7 log log(file)?
8 search search
9 serp serp
10 google google
query brand_term seo_term python_term sitemap_term analysis_term robots_term crawl_term log_term search_term serp_term google_term
0 advertools True False False False False False False False False False False
1 advertools True False False False False False False False False False False
2 log analysis using python False False True False True False False False False False False
3 advertools True False False False False False False False False False False
4 advertools True False False False False False False False False False False

Top terms/topics across all queries

%
python_term 19.4%
robots_term 16.8%
crawl_term 14.7%
analysis_term 11.7%
seo_term 11.3%
sitemap_term 10.4%
serp_term 6.8%
search_term 5.1%
google_term 4.6%
brand_term 0.5%
log_term 0.0%

Convert average positions to SERP page

query position serp_page
0 get emoji 52.333333 6
1 python web crawler 80.500000 9
2 sitemaps.xml 29.250000 3
3 test robots txt 23.000000 3
4 unicode emoji search 37.000000 4
5 how to print emoji in python 42.000000 5
6 search engine 1000 videos 21.000000 3
7 stop words in nlp 51.000000 6
8 url list checker 46.000000 5
9 serps com api 82.000000 9

All reports and charts are made using the full dataset. You can easily use any subset based on available and newly-created columns/criteria

Country comparison (monthly impressions)

  • Click a flag(s) to add/remove countrie(s)

Weighted word frequency - monthly (clicks)

  • Total clicks for all keywords containing <word>.
  • e.g.: in January, keywords containing “python” received a total of 323 clicks

Weighted bigram frequency - monthly (clicks)

Racing chart - impressions

Monthly impressions - map

Racing chart - clicks

Monthly clicks - map

Monthly impressions ~ clicks by country

  • Zoom in to any part of the chart to focus on a certain set of countries (simply drag your mouse across the region/flags of interest)

Top pages monthly (impressions)

  • This chart can be much more useful with larger sites (this one has only ~50 pages)
  • Having URLs split, we can see how segments’ traffic have evoleved over time dir_1, dir_2, or any combination

Query count - monthly

URL count - monthly

Correlation matrix